package com.demo.Test;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

public class Test11 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url= "jdbc:mysql://106.13.7.203:3306/wg12?useCursorFetch=true&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8";
        String username = "root";
        String password = "Asd6250390@";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, username, password);
        System.out.println("连接成功");

        Statement st = conn.createStatement();
        String sql = "select * from users";
        ResultSet rs = st.executeQuery(sql);
        ArrayList<ArrayList> aa = new ArrayList<>();
        while(rs.next()){
            ArrayList a = new ArrayList();
            a.add(rs.getString("id"));
            a.add(rs.getString("name"));
            a.add(rs.getString("gender"));
            a.add(rs.getString("age"));
            aa.add(a);
        }
        System.out.println(aa);
        String[] titleA= {"id", "name", "gender", "age"};
        File file = new File("test2.xls");
        try{
            file.createNewFile();
            WritableWorkbook workbookA = Workbook.createWorkbook(file);
            WritableSheet sheetA = workbookA.createSheet("sheet1", 0);
            Label labelA = null;
            for(int i=0; i< titleA.length; i++){
                labelA = new Label(i, 0, titleA[i]);
                sheetA.addCell(labelA);
            }
            for(int i=0; i<aa.size(); i++){
                for(int j=0; j<4; j++){
                    labelA = new Label(j, i+1, (aa.get(i)).get(j).toString());
                    sheetA.addCell(labelA);
                }
            }
            workbookA.write();
            workbookA.close();
            System.out.println("写入成功");
        }catch (Exception e){

        }
    }
}
